
--region Drop Existing Procedures

IF OBJECT_ID(N'[dbo].[UnderOverOdds_Insert]') IS NOT NULL
	DROP PROCEDURE [dbo].[UnderOverOdds_Insert]

IF OBJECT_ID(N'[dbo].[UnderOverOdds_Update]') IS NOT NULL
	DROP PROCEDURE [dbo].[UnderOverOdds_Update]

IF OBJECT_ID(N'[dbo].[UnderOverOdds_Delete]') IS NOT NULL
	DROP PROCEDURE [dbo].[UnderOverOdds_Delete]

IF OBJECT_ID(N'[dbo].[UnderOverOdds_Select]') IS NOT NULL
	DROP PROCEDURE [dbo].[UnderOverOdds_Select]

--endregion

GO


--region [dbo].[UnderOverOdds_Select]

-- Create By: vdev124
-- Date Generated: Friday, March 04, 2011

CREATE PROCEDURE [dbo].[UnderOverOdds_Select]
	@Page int = null,
	@PageSize int = null,
    @SortBy nvarchar(255) = null,
	@SortType bit = null,
	@IdUnderOverOdd int = null,
	@IdMatch int = null,
	@Total nvarchar(10) = null,
	@Under float = null,
	@Over float = null,
	@OddsType nvarchar(50) = null
AS

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

Declare @RowCount int  
Set @RowCount = (
					Select Count (*) 
					From [dbo].[UnderOverOdds]
					WHERE
					(
						(@IdUnderOverOdd is null OR [UnderOverOdds].[IdUnderOverOdd] = @IdUnderOverOdd)
						AND (@IdMatch is null OR [UnderOverOdds].[IdMatch] = @IdMatch)
						AND (@Total is null OR [UnderOverOdds].[Total] like @Total)
						AND (@Under is null OR [UnderOverOdds].[Under] = @Under)
						AND (@Over is null OR [UnderOverOdds].[Over] = @Over)
						AND (@OddsType is null OR [UnderOverOdds].[OddsType] like @OddsType)
					)
				)
if(@Page is null)
begin
	Set @Page = 1
end

if(@PageSize is null)
begin
	Set @PageSize = @RowCount
end

if(@SortBy is null)
begin
	Set @SortBy = 'DBNull'
end

if(@SortType is null)
begin
	Set @SortType = 1
end

SELECT *
FROM   (
			SELECT [dbo].[UnderOverOdds].*,
                   ROW_NUMBER() OVER 
                   (ORDER BY
                        CASE WHEN @SortBy = 'DBNull' THEN NewID() END ASC,
						CASE WHEN @SortBy = 'IdUnderOverOdd' and @SortType = 1 THEN [UnderOverOdds].[IdUnderOverOdd] END ASC,
						CASE WHEN @SortBy = 'IdUnderOverOdd' and @SortType = 0 THEN [UnderOverOdds].[IdUnderOverOdd] END DESC,
						CASE WHEN @SortBy = 'IdMatch' and @SortType = 1 THEN [UnderOverOdds].[IdMatch] END ASC,
						CASE WHEN @SortBy = 'IdMatch' and @SortType = 0 THEN [UnderOverOdds].[IdMatch] END DESC,
						CASE WHEN @SortBy = 'Total' and @SortType = 1 THEN [UnderOverOdds].[Total] END ASC,
						CASE WHEN @SortBy = 'Total' and @SortType = 0 THEN [UnderOverOdds].[Total] END DESC,
						CASE WHEN @SortBy = 'Under' and @SortType = 1 THEN [UnderOverOdds].[Under] END ASC,
						CASE WHEN @SortBy = 'Under' and @SortType = 0 THEN [UnderOverOdds].[Under] END DESC,
						CASE WHEN @SortBy = 'Over' and @SortType = 1 THEN [UnderOverOdds].[Over] END ASC,
						CASE WHEN @SortBy = 'Over' and @SortType = 0 THEN [UnderOverOdds].[Over] END DESC,
						CASE WHEN @SortBy = 'OddsType' and @SortType = 1 THEN [UnderOverOdds].[OddsType] END ASC,
						CASE WHEN @SortBy = 'OddsType' and @SortType = 0 THEN [UnderOverOdds].[OddsType] END DESC                   
                    ) AS RowNumber 
			FROM [dbo].[UnderOverOdds]
			Where 
			(
				(@IdUnderOverOdd is null OR [UnderOverOdds].[IdUnderOverOdd] = @IdUnderOverOdd)
				AND (@IdMatch is null OR [UnderOverOdds].[IdMatch] = @IdMatch)
				AND (@Total is null OR [UnderOverOdds].[Total] like @Total)
				AND (@Under is null OR [UnderOverOdds].[Under] = @Under)
				AND (@Over is null OR [UnderOverOdds].[Over] = @Over)
				AND (@OddsType is null OR [UnderOverOdds].[OddsType] like @OddsType)
			)
		) AS Temp
WHERE  RowNumber Between (@Page * @PageSize - @PageSize + 1) And @Page * @PageSize
Return @RowCount


--end [dbo].[UnderOverOdds_Select]
--endregion

GO
--=========================================================================================--

	

--region [dbo].[UnderOverOdds_Insert]

-- Create By: vdev124
-- Date Generated: Friday, March 04, 2011

CREATE PROCEDURE [dbo].[UnderOverOdds_Insert]
	@IdUnderOverOdd int OUTPUT,
	@IdMatch int,
	@Total nvarchar(10),
	@Under float,
	@Over float,
	@OddsType nvarchar(50)

AS


INSERT INTO [dbo].[UnderOverOdds] 
(
	[IdMatch],
	[Total],
	[Under],
	[Over],
	[OddsType]
)
VALUES 
(
	@IdMatch,
	@Total,
	@Under,
	@Over,
	@OddsType
)

SET @IdUnderOverOdd = SCOPE_IDENTITY()

--end [dbo].[UnderOverOdds_Insert]
--endregion

GO
--=========================================================================================--

--region [dbo].[UnderOverOdds_Update]

-- Create By: vdev124
-- Date Generated: Friday, March 04, 2011

CREATE PROCEDURE [dbo].[UnderOverOdds_Update]
	@IdUnderOverOdd int,
	@IdMatch int,
	@Total nvarchar(10),
	@Under float,
	@Over float,
	@OddsType nvarchar(50)
AS


UPDATE [dbo].[UnderOverOdds] SET
	[IdMatch] = @IdMatch,
	[Total] = @Total,
	[Under] = @Under,
	[Over] = @Over,
	[OddsType] = @OddsType
WHERE
	[IdUnderOverOdd] = @IdUnderOverOdd

--end [dbo].[UnderOverOdds_Update]
--endregion

GO
--=========================================================================================--

--region [dbo].[UnderOverOdds_Delete]

-- Create By: vdev124
-- Date Generated: Friday, March 04, 2011

CREATE PROCEDURE [dbo].[UnderOverOdds_Delete]
	@IdUnderOverOdd int
AS


DELETE FROM [dbo].[UnderOverOdds]
WHERE
(
	[IdUnderOverOdd] = @IdUnderOverOdd
)

--end [dbo].[UnderOverOdds_Delete]
--endregion

GO
--=========================================================================================--

